unit Importer;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ZAbstractRODataset, ZAbstractDataset, ZDataset,
  ZConnection, ADODB, ZAbstractTable, View, excel_import;

type
  CharSet=Set of Char;
  TForm1 = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    FileLocation: TEdit;
    FileType: TComboBox;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Label4: TLabel;
    ZQuery1: TZQuery;
    ZConnection1: TZConnection;
    ZTable1: TZTable;
    procedure insertIndexing(values:TStringList);
    procedure ExcelRead();
    procedure indexingNama(fileName:string;id:string;nama:string);
//    procedure indexingInisial(id:string;inisial:string);
//    procedure indexingIbu(id:string;ibu:string);
//    procedure indexingTempat(id:string;tempat:string);
//    procedure indexingTanggal(id:string;tanggal:string);
    procedure insertQuery(TableName:TStringList;values:TStringList);
    procedure readValues(TableName:TStringList;Input:string);
    procedure readStatement(Input:string);
    procedure Button1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Split(const Delimiter: Char; // delimiter charachter
                          Input: string;         // input string
                            const Strings: TStrings);
    procedure FormCreate(Sender: TObject); // list of string result
  procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    function StripCharsInSet(s: string; c: CharSet): string;
    { Private declarations }
  public
    { Public declarations }
    formExcel:texcel;
  end;

var
  Form1: TForm1;
  filepath:String;

  fileIndexingNama:string;
  fileIndexingInisial:string;
  fileIndexingIbu:string;
  fileIndexingTempat:string;
  fileIndexingTanggal:string;

implementation

{$R *.dfm}

 procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;


end;

procedure TForm1.Button1Click(Sender: TObject);
var
  openDialog : TOpenDialog;    // Open dialog variable
begin
  // Create the open dialog object - assign to our open dialog variable
  openDialog := TOpenDialog.Create(self);

  // Set up the starting directory to be the current one
  openDialog.InitialDir := GetCurrentDir;

  // Only allow existing files to be selected
  openDialog.Options := [ofFileMustExist];

  // Allow only .dpr and .pas files to be selected
  if(FileType.ItemIndex=1) then
    openDialog.Filter := 'Sql files|*.sql'
  else
    openDialog.Filter := 'Excel files|*.xls';
   // 'Delphi project files|*.dpr|Delphi pascal files|*.pas';

  // Select pascal files as the starting filter type
  //openDialog.FilterIndex := 2;

  // Display the open file dialog
  if openDialog.Execute
  then
  begin
    //ShowMessage('File : '+openDialog.FileName);
    FileLocation.Text:=openDialog.FileName;
    filepath:=openDialog.FileName;
  end
  else Label4.Caption := 'Open file canceled';

  // Free up the dialog
  openDialog.Free;
end;

// memasukkan index
procedure TForm1.insertIndexing(values:TStringList);
var
  myFile    : TextFile;
  text      : string;
  i         : Integer;
  nama      : string;
  count     : Integer;
  countIbu  : Integer;
  list      : TStringList;
  inisial   : string;
  namaIbu   : string;
  namaTempat  : string;
  lahir     : string;
  sentence  :   string;
  Ch        :   Char;
  fileName  : string;
  textList  : TStringList;
  loop      : Integer;
  readText  : string; // string penyimpan pembacaan text readln
  splitter  : TStringList;
  namaExist : bool;
begin
// Try to open the Test.txt file for writing to
  nama:=UpperCase(values[3]);
  count:= 0;
  for I := 1 to Length(nama) do
    if(nama[I]<>' ')then
      count:=count+1;
  list:=TStringList.Create;
  Split(' ', nama, list);
  inisial:='';
  for I := 0 to list.Count-1 do
    inisial:=inisial+list[I][1];
  nama:= nama[1]+nama[Length(nama)];
//  ShowMessage('nama:'+nama);
//  ShowMessage('count:'+IntToStr(count));
//  ShowMessage('inisial:'+inisial);
  namaIbu:=UpperCase(values[6]);
  countIbu:=0;
  for I := 1 to Length(namaIbu) do
    if(namaIbu[I]<>' ')then
      countIbu:=countIbu+1;
  namaIbu:=namaIbu[1]+namaIbu[Length(namaIbu)];
//  ShowMessage('nama ibu:'+namaIbu);
//  ShowMessage('count ibu:'+IntToStr(countIbu));
  namaTempat := UpperCase(values[4]);
  namaTempat:= namaTempat[1]+namaTempat[Length(namaTempat)];
//  ShowMessage('nama tempat:'+namaTempat);
  lahir:= values[5];
  lahir:= lahir[1]+lahir[2]+lahir[4]+lahir[5]+lahir[7]+lahir[8]+lahir[9]+lahir[10];
//  ShowMessage('lahir:'+lahir);
  indexingNama('indexing_nama.txt',values[0], nama);
  indexingNama('indexing_inisial.txt',values[0], inisial);
  indexingNama('indexing_ibu.txt',values[0], namaIbu);
  indexingNama('indexing_tempat.txt',values[0], namaTempat);
  indexingNama('indexing_tanggal.txt',values[0], lahir);
end;

procedure TForm1.indexingNama(fileName:string; id:string; nama:string);
var
  myFile    : TextFile;
  textList  : TStringList;
  loop      : Integer;
  readText  : string; // string penyimpan pembacaan text readln
  splitter  : TStringList;
  namaExist : bool;
  I         : Integer;
begin
  textList := TStringList.Create;
  splitter := TStringList.Create;
  namaExist := false;
  loop := 0;

  if(FileExists(fileName))then // file sudah ada
  begin
//    ShowMessage('File '+fileName+' Exist');
    AssignFile(myFile, fileName);
      Reset(myFile);
      while not Eof(myFile) do
      begin
        Readln(myFile, readText);
        textList.Add(readText);
        loop:=loop+1;
      end;
    CloseFile(myFile);

    // proses string
    for I := 0 to textList.Count-1 do
      begin
        Split(':',textList[I], splitter);
        if(nama=splitter[0])then
        begin
          namaExist:= true;
          textList[I]:=textList[I]+','+id;
        end;
      end;
    if(namaExist<>true)then
    begin
      textList.Add(nama+':'+id);
    end;

    // tulis ulang
    Rewrite(myFile);
    for I := 0 to textList.Count - 1 do
      begin
        Writeln(myFile, textList[I]);
      end;
    CloseFile(myFile);

  end
  else // file belum ada
  begin
//    ShowMessage('File '+fileName+' Not Exist');
    AssignFile(myFile, fileName);
    ReWrite(myFile);
    Writeln(myFile, nama+':'+id);
    CloseFile(myFile);
  end;

end;

// procedure untuk split string
procedure TForm1.Split
(const Delimiter: Char; // delimiter charachter
 Input: string;         // input string
 const Strings: TStrings) ; // list of string result
begin
Assert(Assigned(Strings)) ;
Strings.Clear;
Strings.Delimiter := Delimiter;
Strings.DelimitedText := Input;
end;

procedure TForm1.insertQuery(TableName:TStringList; values:TStringList);
var A:TStringList;
  K: Integer;
  table:String;

    sFile: String;
  sConnectionString: String;
  slTables: TStringList;
  i, j: Integer;
  fField: TField;
  sLine: String;
  sPath: TStrings;
  sTemp: TStrings;
  kota: String;
  kec: String;
  desa: String;
  idkota: String;
  kkota:String;
  idkec: String;
  idesa: String;
  ktemp:String;
  tanggal:String;
  idtgl:String;
  vallist: String;
  tulis : Boolean;
  stata : Integer;
begin
  kota :='';
  for K := 2 to TableName.Count-1 do
  begin

    kota:=kota+TableName[K];
    if(K<>TableName.Count-1)then
      kota:=kota+' ';
  end;
  kota := AnsiUpperCase(kota);
  kec:=values[12];
  desa:=values[13];
//  ShowMessage('nama kota : '+kota+', kec:'+kec+', desa:'+desa);



  //cek
  ZTable1.Active := False;
  ZTable1.TableName := 'kabkot';
  ZTable1.Filter := 'NAMA = '+QuotedStr(kota);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  if (ZTable1.Fields[1].AsString = '') then
    begin
    sTemp := TStringList.Create;
    ExtractStrings([' '], [], PChar(kota), sTemp);
    kkota := '';
    for i := 1 to sTemp.Count - 1 do
       kkota := kkota + sTemp[i];
    ktemp := kkota[1]+kkota[Length(kkota)];
    with ZQuery1 do  //masukkin nih gan
      begin
        Active:=False;
        sql.Clear;
        sql.Text := 'INSERT INTO kabupaten (KODE,NAMA) VALUES ("'+ktemp+'","'+kota+'")';
//        ShowMessage('insert kota');
        ExecSQL;
      end;
    end;
  //get id kota
  ZTable1.Active := False;
  ZTable1.TableName := 'kabkot';
  ZTable1.Filter := 'NAMA = '+QuotedStr(kota);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  idkota:=ZTable1.Fields[1].AsString;
//  ShowMessage('ikota='+idkota);
  //----------------------KEC
  sTemp := TStringList.Create;
  kec := AnsiUpperCase(kec);
  ExtractStrings([' '], [], PChar(kec), sTemp);
  kec:='';
  for i := 0 to sTemp.Count - 1 do
       kec := kec + sTemp[i];
//       ShowMessage('kec : ='+kec);
  //cek ada tidak
  ZTable1.Active := False;
  ZTable1.TableName := 'kecamatan';
  ZTable1.Filter := 'NAMA = '+QuotedStr(kec)+' and KODE_KABKOT = '+QuotedStr(idkota);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  if (ZTable1.Fields[1].AsString = '') then
    begin
    ktemp := kec[1]+kec[Length(kec)];
//    ShowMessage('ktemp : '+ktemp);
      with ZQuery1 do  //masukkin nih gan
        begin
          Active:=False;
          sql.Clear;
          sql.Text := 'INSERT INTO kecamatan (KODE_KABKOT,KODE_KECAMATAN,NAMA) VALUES ("'+idkota+'","'+idkota+ktemp+'","'+kec+'")';
//          ShowMessage('insert kecamatan');
//          ExecSQL;
        end;
    end;
  //get id kec
  ZTable1.Active := False;
  ZTable1.TableName := 'kecamatan';
  ZTable1.Filter := 'NAMA = '+QuotedStr(kec)+' and KODE_KABKOT = '+QuotedStr(idkota);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  idkec := ZTable1.Fields[2].AsString;
//  ShowMessage('ikec='+idkec);
  //----------------------DESA
  sTemp := TStringList.Create;
  desa := AnsiUpperCase(desa);
  ExtractStrings([' '], [], PChar(desa), sTemp);
  desa:='';
  for i := 0 to sTemp.Count - 1 do
       desa := desa + sTemp[i];
//       ShowMessage('desa:'+desa);
  //cek ada tidak
  ZTable1.Active := False;
  ZTable1.TableName := 'desa';
  ZTable1.Filter := 'NAMA = '+QuotedStr(desa)+' and KODE_KABKOT = '+QuotedStr(idkota);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  if (ZTable1.Fields[1].AsString = '') then
    begin
    ktemp := desa[1]+desa[Length(desa)];
      with ZQuery1 do  //masukkin nih gan
        begin
          Active:=False;
          sql.Clear;
          sql.Text := 'INSERT INTO desa (KODE_KABKOT,KODE_KECAMATAN,KODE_DESA,NAMA) VALUES ("'+idkota+'","'+idkec+'","'+idkec+ktemp+'","'+desa+'")';
//          ShowMessage('insert desa');
          ExecSQL;
        end;
    end;
  ZTable1.Active := False;
  ZTable1.TableName := 'desa';
  ZTable1.Filter := 'NAMA = '+QuotedStr(desa)+' and KODE_KABKOT = '+QuotedStr(idkota);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  idesa := ZTable1.Fields[3].AsString;
//      ShowMessage('idesa='+idesa);
  //INSERT tanggal
  tanggal := FormatDateTime('yyyy-mm-dd hh:mm:ss', Now());
  with ZQuery1 do  //masukkin nih gan
    begin
      Active:=False;
      sql.Clear;
      sql.Text := 'INSERT INTO tanggal (TANGGAL) VALUES ("'+tanggal+'")';
//      ShowMessage('insert tanggal');
      ExecSQL;
    end;
  ZTable1.Active := False;
  ZTable1.TableName := 'tanggal';
  ZTable1.SortedFields:='ID_TANGGAL';
  ZTable1.SortType := stDescending;
  ZTable1.Active := True;
  ZTable1.First;
  idtgl := ZTable1.Fields[1].AsString;
//    ShowMessage(idtgl);
  ZTable1.SortedFields:='';


  ZQuery1.Connection := ZConnection1;
  ZQuery1.SQL.Clear;
  table:='pemilih_'+idesa;
//  ShowMessage('nama table :'+table);
  ZQuery1.SQL.Add('CREATE TABLE IF NOT EXISTS `'+table+'` ('+
  '`ID_USER` int(11) NOT NULL AUTO_INCREMENT,'+
  '`NIK` char(36) NOT NULL,'+
  '`NKK` char(36) NOT NULL,'+
  '`NAMA` char(50) NOT NULL,'+
  '`TEMPAT` char(36) NOT NULL,'+
  '`TANGGAL` char(10) NOT NULL,'+
  '`IBU` char(36) NOT NULL,'+
  '`AGAMA` char(36) NOT NULL,'+
  '`KAWIN` char(1) NOT NULL,'+
  '`KELAMIN` char(1) NOT NULL,'+
  '`KELAINAN` char(36) NOT NULL,'+
  '`ALAMAT` char(150) NOT NULL,'+
  '`RW` char(3) NOT NULL,'+
  '`RT` char(3) NOT NULL,'+
  '`KET` char(150) NOT NULL,'+
  '`ID_TANGGAL` int(11) NOT NULL,'+
  'PRIMARY KEY (`ID_USER`)'+
') ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;');
     ZQuery1.ExecSQL;

    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Add('INSERT INTO `'+table+'` (`NIK`, `NKK`, `NAMA`, `TEMPAT`, `TANGGAL`, '+
    '`IBU`, `AGAMA`, `KAWIN`, `KELAMIN`, `KELAINAN`, `ALAMAT`, `RW`, `RT`, `KET`, `ID_TANGGAL`) VALUES'
    +'('''+values[1]+'''	, '''+values[2]+''', '''+values[3]+''', '''+values[4]+''', '''+values[5]+''', '''+values[6]+''', '''+values[7]+''', '''+values[8]+''', '''+values[9]+''', '''+
    values[10]+''', '''+values[11]+''', '''+values[14]+''', '''+values[15]+''', '''+values[16]+''', '''+idtgl+''');');
    ZQuery1.ExecSQL;

    //cek ada tidak
  ZTable1.Active := False;
  ZTable1.TableName := 'penyimpan';
  ZTable1.Filter := 'NAMA = '+QuotedStr(table);
  ZTable1.Filtered := True;
  ZTable1.Active := True;
  ZTable1.First;
  if (ZTable1.Fields[1].AsString = '') then
    begin
      with ZQuery1 do  //masukkin nih gan
        begin
          Active:=False;
          sql.Clear;
          sql.Text := 'INSERT INTO penyimpan (NAMA) VALUES ("'+table+'")';
//          ShowMessage('insert desa');
          ExecSQL;
        end;
    end;

    insertIndexing(values);
end;

procedure TForm1.readValues(TableName:TStringList; Input: string);
var
  counter: Integer;
  statement: string;
  A: TStringList;
  I: Integer;
begin
  A := TStringList.Create;
  counter := 1;
  while (Input[counter] <> ';') do
  begin
    if (Input[counter] = ' ') then
    begin
      // ShowMessage('spasi');
      counter := counter + 1;
    end
    else if (Input[counter] = '''') then
    begin
      statement := '';
      counter := counter + 1;

      while (Input[counter] <> '''') do // baca sampe abis
      begin
        statement := statement + Input[counter];
        counter := counter + 1;
      end;
      counter := counter + 1;
      A.Add(statement);
      // ShowMessage(statement);
    end
    else if (Input[counter] = ',') then
    begin
      // ShowMessage('koma');
      counter := counter + 1;
    end
    else
    begin
      // ShowMessage('else '+input[counter]);
      statement := '';
      while ((Input[counter] <> ' ') and (Input[counter] <> ',')) do
      begin
        statement := statement + Input[counter];
        counter := counter + 1;
      end;
      A.Add(statement);
      // ShowMessage(statement);
    end;
  end;
  Form1.insertQuery(TableName, A);
//  for I := 0 to A.Count - 1 do
//  begin
//    ShowMessage(A[I]);
//  end;
end;

procedure TForm1.readStatement(Input: string);
var
  posisi: Integer;
  sentence: string;
  counter: Integer;
  statement: string;
  A: TStringList;
  finish: bool;

  startQuotes: bool;
  TableName:TStringList;
  I: Integer;

begin
  counter:=Pos('`', Input)+1;
  statement:='';
  while(Input[counter]<>'`')do
  begin
    statement:=statement+Input[counter];
    counter:=counter+1;
  end;
  TableName:= TStringList.Create;
  Split('_', statement, TableName);
//  ShowMessage('statement:'+statement);
//  for I := 2 to TableName.Count-1 do
//  begin
//    ShowMessage('nama table : '+TableName[I]);
//  end;

  posisi := Pos('values', Input);
  counter := posisi;
  statement := '';
  startQuotes := false;

  finish := false;

  while (Input[counter] <> ';') do
  begin
    if (Input[counter] = '(') then
    begin
      counter := counter + 1;
      statement := '';
      while (Input[counter] <> ')') do
      begin
        statement := statement + Input[counter];
        counter := counter + 1;
      end;
      readValues(TableName, statement + ';');
    end
    else
    begin
      counter := counter + 1;
    end;
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  myFile: TextFile;
  Ch: Char;
  sentence: string;
  posisi: Integer;
  counter: Integer;
  statement: string;
  f:TForm2;
  I: Integer;
begin
  if(FileType.ItemIndex=0)then
  begin
//    ExcelRead;
    formExcel := texcel.Create(Application, FileLocation.text);
  end
  else if(FileType.ItemIndex=1)then
  begin // sql
      AssignFile(myFile, FileLocation.text);
      Reset(myFile);
      sentence := '';
      while not Eof(myFile) do
      begin
        Read(myFile, Ch);
        sentence := sentence + Ch;
      end;

      sentence := LowerCase(sentence);
      posisi := Pos('insert', sentence);
      counter := posisi;
      statement := '';
      while (sentence[counter] <> ';') do
      begin
        statement := statement + sentence[counter];
        counter := counter + 1;

      end;
      statement := statement + ';';
      // ShowMessage(statement);
      readStatement(statement);
  end


end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  Close;
end;

procedure TForm1.ExcelRead();
var
  sFile: String;
  sConnectionString: String;
  slTables: TStringList;
  i, j: Integer;
  fField: TField;
  sLine: String;
  sPath: TStrings;
  sTemp: TStrings;
  kota: String;
  kec: String;
  desa: String;
  idkota: String;
  kkota:String;
  idkec: String;
  idesa: String;
  ktemp:String;
  tanggal:String;
  idtgl:String;
  vallist: String;
  tulis : Boolean;
  stata : Integer;
  rcount:Integer;
begin
    Label4.Caption := 'Proses...';
    // Get the file they selected
    sFile := FileLocation.Text;  //ganti folder dialog??
    //PATH
    sPath := TStringList.Create;
    try
      ExtractStrings(['\'], [], PChar(sFile), sPath);
      desa := sPath[sPath.Count-1];
      kec := sPath[sPath.Count-2];
      kota := sPath[sPath.Count-3];
      desa := Copy(desa,0,(Length(desa)-4));
      //----------------------KOTA
      kota := AnsiUpperCase(kota);
      //cek
      ZTable1.Active := False;
      ZTable1.TableName := 'kabkot';
      ZTable1.Filter := 'NAMA = '+QuotedStr(kota);
      ZTable1.Filtered := True;
      ZTable1.Active := True;
      ZTable1.First;
      if (ZTable1.Fields[1].AsString = '') then
        begin
        sTemp := TStringList.Create;
        ExtractStrings([' '], [], PChar(kota), sTemp);
        kkota := '';
        for i := 1 to sTemp.Count - 1 do
           kkota := kkota + sTemp[i];
        ktemp := kkota[1]+kkota[Length(kkota)];
        with ZQuery1 do  //masukkin nih gan
          begin
            Active:=False;
            sql.Clear;
            sql.Text := 'INSERT INTO kabupaten (KODE,NAMA) VALUES ("'+ktemp+'","'+kota+'")';
            ExecSQL;
          end;
        end;
      //get id kota
      ZTable1.Active := False;
      ZTable1.TableName := 'kabkot';
      ZTable1.Filter := 'NAMA = '+QuotedStr(kota);
      ZTable1.Filtered := True;
      ZTable1.Active := True;
      ZTable1.First;
      idkota:=ZTable1.Fields[1].AsString;
      //ShowMessage('ikota='+idkota);
      //----------------------KEC
      sTemp := TStringList.Create;
      kec := AnsiUpperCase(kec);
      ExtractStrings([' '], [], PChar(kec), sTemp);
      kec:='';
      for i := 1 to sTemp.Count - 1 do
         begin
          if (i=1) then
            kec := kec + sTemp[i]
          else
            kec := kec +' '+sTemp[i];
         end;
      //cek ada tidak
      ZTable1.Active := False;
      ZTable1.TableName := 'kecamatan';
      ZTable1.Filter := 'NAMA = '+QuotedStr(kec)+' and KODE_KABKOT = '+QuotedStr(idkota);
      ZTable1.Filtered := True;
      ZTable1.Active := True;
      ZTable1.First;
      if (ZTable1.Fields[1].AsString = '') then
        begin
        ktemp := kec[1]+kec[Length(kec)];
          with ZQuery1 do  //masukkin nih gan
            begin
              Active:=False;
              sql.Clear;
              sql.Text := 'INSERT INTO kecamatan (KODE_KABKOT,KODE_KECAMATAN,NAMA) VALUES ("'+idkota+'","'+idkota+ktemp+'","'+kec+'")';
              ExecSQL;
            end;
        end;
      //get id kec
      ZTable1.Active := False;
      ZTable1.TableName := 'kecamatan';
      ZTable1.Filter := 'NAMA = '+QuotedStr(kec)+' and KODE_KABKOT = '+QuotedStr(idkota);
      ZTable1.Filtered := True;
      ZTable1.Active := True;
      ZTable1.First;
      idkec := ZTable1.Fields[2].AsString;
      //ShowMessage('ikec='+idkec);
      //----------------------DESA
      sTemp := TStringList.Create;
      desa := AnsiUpperCase(desa);
      ExtractStrings([' '], [], PChar(desa), sTemp);
      desa:='';
      for i := 1 to sTemp.Count - 1 do
           begin
            if (i=1) then
              desa := desa + sTemp[i]
            else
              desa := desa +' '+sTemp[i];
           end;
      //cek ada tidak
      ZTable1.Active := False;
      ZTable1.TableName := 'desa';
      ZTable1.Filter := 'NAMA = '+QuotedStr(desa)+' and KODE_KABKOT = '+QuotedStr(idkota);
      ZTable1.Filtered := True;
      ZTable1.Active := True;
      ZTable1.First;
      if (ZTable1.Fields[1].AsString = '') then
        begin
        ktemp := desa[1]+desa[Length(desa)];
          with ZQuery1 do  //masukkin nih gan
            begin
              Active:=False;
              sql.Clear;
              sql.Text := 'INSERT INTO desa (KODE_KABKOT,KODE_KECAMATAN,KODE_DESA,NAMA) VALUES ("'+idkota+'","'+idkec+'","'+idkec+ktemp+'","'+desa+'")';
              ExecSQL;
            end;
        end;
      ZTable1.Active := False;
      ZTable1.TableName := 'desa';
      ZTable1.Filter := 'NAMA = '+QuotedStr(desa)+' and KODE_KABKOT = '+QuotedStr(idkota);
      ZTable1.Filtered := True;
      ZTable1.Active := True;
      ZTable1.First;
      idesa := ZTable1.Fields[3].AsString;
//      ShowMessage('idesa='+idesa);
      //INSERT tanggal
      tanggal := FormatDateTime('yyyy-mm-dd hh:mm:ss', Now());
      with ZQuery1 do  //masukkin nih gan
        begin
          Active:=False;
          sql.Clear;
          sql.Text := 'INSERT INTO tanggal (TANGGAL) VALUES ("'+tanggal+'")';
          ExecSQL;
        end;
      ZTable1.Active := False;
      ZTable1.TableName := 'tanggal';
      ZTable1.SortedFields:='ID_TANGGAL';
      ZTable1.SortType := stDescending;
      ZTable1.Active := True;
      ZTable1.First;
      idtgl := ZTable1.Fields[1].AsString;
  //    ShowMessage(idtgl);
      ZTable1.SortedFields:='';
      //CREATE TABLE CUY
      with ZQuery1 do  //masukkin nih gan
        begin
          //pemilih
          Active:=False;
          sql.Clear;
          sql.Text := 'CREATE TABLE IF NOT EXISTS pemilih_'+idesa+'('+
                        'ID_USER int(11) NOT NULL AUTO_INCREMENT,'+
                        'NIK char(36) NOT NULL,'+
                        'NKK char(36) NOT NULL,'+
                        'NAMA char(50) NOT NULL,'+
                        'TEMPAT char(36) NOT NULL,'+
                        'TANGGAL char(10) NOT NULL,'+
                        'IBU char(36) NOT NULL,'+
                        'AGAMA char(36) NOT NULL,'+
                        'KAWIN char(1) NOT NULL,'+
                        'KELAMIN char(1) NOT NULL,'+
                        'KELAINAN char(36) NOT NULL,'+
                        'ALAMAT char(150) NOT NULL,'+
                        'RW char(3) NOT NULL,'+
                        'RT char(3) NOT NULL,'+
                        'KET char(150) NOT NULL,'+
                        'ID_TANGGAL int(11) NOT NULL,'+
                        'PRIMARY KEY (ID_USER))';
          ExecSQL;
          //penyimpan
          Active:=False;
          sql.Clear;
          sql.Text := 'INSERT INTO penyimpan (NAMA) VALUES ("pemilih_'+idesa+'")';
          ExecSQL;
        end;
      finally
        sPath.Free;
        sTemp.Free;
    end;
    //PATH_end

    // ADO will error if try to connect when already open, so ensure closed
    if ADOConnection1.Connected then
    begin
      ADOConnection1.Close;
    end;
    // Build connection string using the Microsoft Access Database Engine 2010
    sConnectionString :=
      'Provider=Microsoft.ACE.OLEDB.12.0;' +
      'Data Source="' + sFile + '";' +
      'Extended Properties="Excel 8.0;HDR=NO;"';
    // Assign the connection string to the ADO component
    ADOConnection1.ConnectionString := sConnectionString;
    ADOConnection1.Connected := True;
    // Connect to the provider
    slTables := TStringList.Create;
    try
      // Get list of sheets (as tables)
      ADOConnection1.GetTableNames(slTables);
      // Go through each sheet(table) and query it
      ADOQuery1.Connection := ADOConnection1;
      for i := 0 to slTables.Count - 1 do
      begin
        // Query the sheet(table)
        ADOQuery1.Active := False;
        ADOQuery1.SQL.Text := 'SELECT * FROM `' + slTables.Strings[i] + '`';
        ADOQuery1.Active := True;
        if not ADOQuery1.IsEmpty then
        begin
          // There were rows
          tulis := False;
          try
            // Loop through the rows and output to CSV
            ADOQuery1.First;
            rcount:=0;
            while not ADOQuery1.Eof do
            begin
              sLine := '';
              stata :=0;
              rcount:=rcount+1;
              // Loop through the columns
              for j := 0 to ADOQuery1.FieldCount-1 do
              begin
                fField := ADOQuery1.Fields[j];
                //IDE PROGRAM
                if (tulis) then
                begin
                  case stata of
                  {
                  stata 0 : NIK
                  stata 1 : NKK
                  stata 2 : Nama
                  stata 3 : TempatLahir
                  stata 4 : TglLahir
                  stata 5 : Nama Ibu
                  stata 6 : Agama
                  stata 7 : Perkawinan
                  stata 8 : JenisKelamin
                  stata 9 : Kelainan
                  stata 10 : Alamat
                  stata 11 : RW
                  stata 12 : RT
                  stata 13 : Ket
                  }
                    0 : begin
                        if (j=0) then
                        begin
                          sLine := sLine+'('+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    1 : begin
                        if (j=1) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    2 : begin
                        if (j=2) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    3 : begin
                        if (j=3) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    4 : begin
                        if (j=4) then
                        begin
                          sLine := sLine+QuotedStr(fField.AsString)+',';
                          Inc(stata);
                        end;
                    end;
                    5 : begin
                        if (j=5) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    6 : begin
                        if (j=6) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    7 : begin
                        if (j=7) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    8 : begin
                        if (j=8) then
                        begin
                          sLine := sLine+QuotedStr(StripCharsInSet(fField.AsString,[#10..#32,#44..#46,#48..#57,#65..#90,#97..#122]))+',';
                          Inc(stata);
                        end;
                    end;
                    9 : begin
                        if (j=9) then
                        begin
                          sLine := sLine+QuotedStr(fField.AsString)+',';
                          Inc(stata);
                        end;
                    end;
                    10 : begin
                        if (j=10) then
                        begin
                          sLine := sLine+QuotedStr(fField.AsString)+',';
                          Inc(stata);
                        end;
                    end;
                    11 : begin
                        if (j=11) then
                        begin
                          sLine := sLine+QuotedStr(fField.AsString)+',';
                          Inc(stata);
                        end;
                    end;
                    12 : begin
                        if (j=12) then
                        begin
                          sLine := sLine+QuotedStr(fField.AsString)+',';
                          Inc(stata);
                        end;
                    end;
                    13 : begin
                        if (j=13) then
                        begin
                          sLine := sLine+QuotedStr(fField.AsString)+','+idtgl+')';
                          stata:=0;
                        end;
                    end;
                  end;
                end;
                if ((j = 0) and (fField.AsString = '1') and (not tulis)) then
                   begin
                    tulis := True;
                    break;
                   end
                else if ((j = 0) and (fField.AsString = '') and (tulis)) then
                   begin
                      tulis := False;
                      sLine := '';
                      break;
                   end;
              end;
              // TODO: Skip blank lines?
              if (sLine <> '') then
                begin
                  sLine := sLine + ',';
                  vallist := vallist+sLine;
                  //EDIT LAGI GAN
                  if (rcount>999) then
                    begin
                      vallist := Copy(vallist,0,(Length(vallist)-1));
                      vallist := 'INSERT INTO pemilih_'+idesa+' (NIK,NKK,NAMA,TEMPAT,TANGGAL,IBU,AGAMA,KAWIN,KELAMIN,KELAINAN,ALAMAT,RW,RT,KET,ID_TANGGAL) VALUES '+vallist+';';
                      with ZQuery1 do  //masukkin nih gan
                        begin
                          Active:=False;
                          sql.Clear;
                          sql.Text := vallist;
                          ExecSQL;
                        end;
                      rcount:=0;
                      vallist:='';
                    end;
                end;
              ADOQuery1.Next;
            end;
            finally
              //free csv
              tulis := False;
            end;
        end;
        ADOQuery1.Active := False;
      end;
      //INSERT INTO table
      if (vallist<>'') then
        begin
        vallist := Copy(vallist,0,(Length(vallist)-1));
        vallist := 'INSERT INTO pemilih_'+idesa+' (NIK,NKK,NAMA,TEMPAT,TANGGAL,IBU,AGAMA,KAWIN,KELAMIN,KELAINAN,ALAMAT,RW,RT,KET,ID_TANGGAL) VALUES '+vallist+';';
        with ZQuery1 do  //masukkin nih gan
          begin
            Active:=False;
            sql.Clear;
            sql.Text := vallist;
            ExecSQL;
          end;
        end;
    finally
      slTables.Free;
    end;
    ADOConnection1.Close;
    Label4.Caption := 'Selesai';
    ShowMessage('Excel selesai diproses');
    FileLocation.Text:='';
  end;

function TForm1.StripCharsInSet(s:string; c:CharSet):string;
  var i,j:Integer;
  begin
     SetLength(result,Length(s));
     j:=0;
     for i:=1 to Length(s) do
       if (s[i] in c) then
        begin
         inc(j);
         result[j]:=s[i];
        end;
     SetLength(result,j);
  end;

procedure TForm1.FormCreate(Sender: TObject);
var mfile:Textfile;
a:tstringlist;
i:integer;

begin
  ZConnection1.Connected := True;
  Label4.Caption := '';
  fileIndexingNama:='indexing_nama.txt';
  fileIndexingInisial:='indexing_inisial.txt';
  fileIndexingIbu:='indexing_ibu.txt';
  fileIndexingTempat:='indexing_tempat.txt';
  fileIndexingTanggal:='indexing_tanggal.txt';

//  a:=tstringlist.create;
//  for I := 0 to 40000000 do
//    a.Add('lalalalalalala');
//    Showmessage('finish');

//  with Ztable1 do
//  begin
//    active:=false;
//    tableName:='pemilih_75060003';
//    active:=true;
//    first;
//    AssignFile(mfile, 'fileGede.txt');
//    rewrite(mfile);
//    while not Eof do
//    begin
//      writeln(mfile, FieldByName('NAMA').AsString);
//      next;
//    end;
//    closeFile(mfile);
//  end;
end;

end.
